package com.lvmama.pet.sync.service.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import javax.script.Invocable;
import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;
import javax.sql.DataSource;

import com.lvmama.comm.sync.pojo.SyncTrigger;
import com.lvmama.pet.sync.service.SyncDataContext;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.support.JdbcUtils;

import com.lvmama.comm.bee.service.sync.SyncNewException;
import com.lvmama.comm.bee.service.sync.SyncBaseExecuteService;
import com.lvmama.pub.DistributedContext;
import com.lvmama.pub.SqlAnalyzer;

@SuppressWarnings("rawtypes")
public class SyncExecutor {

	private static final Log logger = LogFactory.getLog(SyncExecutor.class);

	static SyncExecutor instance;
	
	static Object LOCK = new Object();
	
	private static final Map<String,ScriptEngine> scriptEngineMap=new ConcurrentHashMap<String,ScriptEngine>();
	
	private SyncExecutor() {
	}
	
	public static final SyncExecutor getDefault() {
		synchronized (LOCK) {
			if (instance == null) {
				instance = new SyncExecutor();
			}
		}
		return instance;
	}
	
	public List executeScriptQuery(String script, String sourceId, Map<String, DataSource> dataSourceMap) throws Exception {		
		ScriptEngine engine = getScriptEngineByScript(script);
		try {
			engine.eval(script);
		} catch (ScriptException e) {
			throw new SyncNewException("GROOVY脚本有误\r\n" + e.toString());
		}
		try {
			return (List) ((Invocable) engine).invokeFunction("query", sourceId, dataSourceMap);
		} catch (NoSuchMethodException e) {
			throw new SyncNewException("GROOVY脚本中没有找到方法\r\n java.util.List query(String, Map<String, javax.sql.DataSource>)");
		} catch (Exception e) {
			throw new SyncNewException("GROOVY脚本错误\r\n" + e.getMessage());
		}
	}
	
	public int executeScriptUpdate(String script, Object param,
			Map<String, DataSource> dataSourceMap, String sourceId) throws Exception {
		ScriptEngine engine = getScriptEngineByScript(script);
		try {
			engine.eval(script);
		} catch (ScriptException e) {
			throw new SyncNewException("GROOVY脚本有误\r\n" + e.toString());
		}
		try {
			return (Integer) ((Invocable) engine).invokeFunction("execute", param, dataSourceMap, sourceId);
		} catch (NoSuchMethodException e) {
			throw new SyncNewException("GROOVY脚本中没有找到方法\r\n Integer execute(Object, Map<String, javax.sql.DataSource>, String)");
		} catch (Exception e) {
			throw new SyncNewException("GROOVY脚本错误\r\n" + e.getMessage());
		}
	}
	
	private ScriptEngine getScriptEngineByScript(String script){
		ScriptEngine engine=scriptEngineMap.get(script);
		if(engine==null){
			engine=new ScriptEngineManager().getEngineByName("groovy");
			scriptEngineMap.put(script, engine);
		}
		
		return engine;
	}

	public List executeSQLQuery(String script, String sourceId, DataSource dataSource) throws Exception {
		
		List<String> paramKeyList = JDBCUtils.createParamKeyList(script);
		String querySql = StringUtils.trimToNull(script);
		if (!querySql.toUpperCase().startsWith("SELECT")) {
			throw new SyncNewException("源脚本必须以SELECT开头");
		}
		querySql = JDBCUtils.createPrepareStatementSql(querySql);
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			Map<String, Object> param = new HashMap<String, Object>();
			param.put(SyncBaseExecuteService.PK_VALUE_KEY, sourceId);
			con = dataSource.getConnection();
			String _querySql =executeBatchPkValue(paramKeyList,param,querySql,script,false);
			ps = con.prepareStatement(_querySql);
			JDBCUtils.setPreparedStatementParams(paramKeyList, ps, param);
			rs = ps.executeQuery();
			return JDBCUtils.catchResult(rs);
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(ps);
			JdbcUtils.closeConnection(con);
		}
		
	}

	public List executeSQLQueryTest(String script, String sourceId, DataSource dataSource) throws Exception {

		List<String> paramKeyList = JDBCUtils.createParamKeyList(script);
		String querySql = StringUtils.trimToNull(script);
		if (!querySql.toUpperCase().startsWith("SELECT")) {
			throw new SyncNewException("源脚本必须以SELECT开头");
		}
		querySql = JDBCUtils.createPrepareStatementSql(querySql);
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			Map<String, Object> param = new HashMap<String, Object>();
			param.put(SyncBaseExecuteService.PK_VALUE_KEY, sourceId);
			con = dataSource.getConnection();
			String _querySql =executeBatchPkValue(paramKeyList,param,querySql,script,true);
			ps = con.prepareStatement(_querySql);
			JDBCUtils.setPreparedStatementParams(paramKeyList, ps, param);
			rs = ps.executeQuery();
			return JDBCUtils.catchResult(rs);
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(ps);
			JdbcUtils.closeConnection(con);
		}

	}

	public String executeBatchPkValue(List<String> paramKeyList,Map<String, Object> param,String querySql,String script,boolean isTest){
		String _querySql = querySql;
		try {
			StringBuffer stringBuffer = new StringBuffer();
			SyncTrigger trigger = (SyncTrigger)SyncDataContext.get(SyncDataContext.TRIGGER_DATA);
			String bathcSourceId ="";
			if(isTest){
				 bathcSourceId = trigger.getPkValue();
			}else{
				 bathcSourceId = trigger.getBatchPkValue();

			}
			//支持in方式
			if(bathcSourceId.indexOf(",")>0){
                String[] params = bathcSourceId.split(",");
                paramKeyList.clear();
                param.clear();
                for (int n = 0; n <= params.length-1; n++)
                {
                    stringBuffer.append("?,");
                    paramKeyList.add(params[n]);
                    param.put(params[n],params[n]);
                }
                stringBuffer.deleteCharAt(stringBuffer.length() - 1);
                _querySql= script.replace("#_ID_#",stringBuffer.toString());
            }
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		}
		return _querySql;
	}
	public List executeSQLQuery(String script, Map param, DataSource dataSource) throws Exception {
		
		List<String> paramKeyList = JDBCUtils.createParamKeyList(script);
		String querySql = StringUtils.trimToNull(script);
		querySql = JDBCUtils.createPrepareStatementSql(querySql);
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = dataSource.getConnection();
			ps = con.prepareStatement(querySql);
			JDBCUtils.setPreparedStatementParams(paramKeyList, ps, param);
			rs = ps.executeQuery();
			return JDBCUtils.catchResult(rs);
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(ps);
			JdbcUtils.closeConnection(con);
		}
		
	}

	public int executeSQLUpdate(String script, Object param,
			DataSource dataSource, String sourceId) throws Exception {
		return executeSqlUpdate(script, param, dataSource, sourceId, false);
	}
	
	public int testExecuteSQLUpdate(String script, Object param,
			DataSource dataSource, String sourceId) throws Exception {
		return executeSqlUpdate(script, param, dataSource, sourceId, true);
	}
	
	private int executeSqlUpdate(String script, Object param,
			DataSource dataSource, String sourceId, boolean test) throws Exception {
		List<String> paramKeyList = JDBCUtils.createParamKeyList(script);
		String querySql = StringUtils.trimToNull(script);
		if (!querySql.toUpperCase().startsWith("UPDATE") &&
				!querySql.toUpperCase().startsWith("INSERT") &&
				!querySql.toUpperCase().startsWith("DELETE")) {
			throw new SyncNewException("目标脚本必须以UPDATE,INSERT或DELETE开头");
		}
		querySql = JDBCUtils.createPrepareStatementSql(querySql);
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = dataSource.getConnection();
			con.setAutoCommit(!test);
			ps = con.prepareStatement(querySql);
			Map par = JDBCUtils.createMapParamByObject(paramKeyList, param, sourceId);
			JDBCUtils.setPreparedStatementParams(paramKeyList, ps, par);
			int row = ps.executeUpdate();
			if (test) {
				con.rollback();
			}
			
			if(SqlAnalyzer.isWrite(script)){
				DistributedContext.getDBWriteRecord().updated(null);
			}
			
			return row;
		} finally {
			JdbcUtils.closeStatement(ps);
			JdbcUtils.closeConnection(con);
		}
	}

}
